{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-31T00:41:47.365137Z",
     "start_time": "2018-08-31T00:41:39.934877Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "from datetime import datetime\n",
    "pd.set_option('display.max_columns', 50)\n",
    "pd.set_option('display.max_rows', 100)\n",
    "pd.set_option('display.float_format', lambda x: '%.3f' % x)\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "## 1.1 定义一下路径\n",
    "Path = 'D:\\APViaML'\n",
    "Y_factor_name = 'ret_annual'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-31T00:41:47.378673Z",
     "start_time": "2018-08-31T00:41:47.374160Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_rawdata_sas():\n",
    "    # load data\n",
    "    raw_data = pd.read_sas(Path + '\\\\data\\\\final_data_annual.sas7bdat')\n",
    "    return raw_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:22:34.073234Z",
     "start_time": "2018-08-28T06:22:34.067219Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_top_data(temp_data,factor='mve',get_type='top',keep_num=1000):\n",
    "    temp_data.sort_values([factor],ascending=False,inplace=True)\n",
    "    index1 = pd.Series(temp_data.index)\n",
    "    index1.drop_duplicates(inplace=True)\n",
    "    index1  = index1.apply(lambda x:datetime.strftime(x, '%Y-%m'))\n",
    "    index1 = list(index1)\n",
    "    temp_data['num'] = 0\n",
    "    if get_type == 'top':\n",
    "        for i in index1 :\n",
    "            temp_data.loc[i,'num'] = temp_data.loc[i,'num'].reset_index().index.values\n",
    "    elif get_type=='bottom':\n",
    "        for i in index1 :\n",
    "            temp_data.loc[i,'num'] = len(temp_data.loc[i,'num']) - temp_data.loc[i,'num'] .reset_index().index.values       \n",
    "    top_100_factor3_data = temp_data[temp_data['num']<keep_num]\n",
    "    del temp_data['num']\n",
    "    return top_100_factor3_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:22:34.083261Z",
     "start_time": "2018-08-28T06:22:34.075239Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_X_data(raw_data,drop_list,scaler_type='MinMax'):\n",
    "    def remove_list(df,factor):\n",
    "        for x in factor:\n",
    "            df.remove(x)\n",
    "        return df\n",
    "    X_factor_list = list(raw_data.columns)\n",
    "    X_factor_list = remove_list(df=X_factor_list,factor=drop_list)\n",
    "    X_all_factor_data = raw_data.loc[:,X_factor_list]\n",
    "    if scaler_type == 'MinMax':\n",
    "        scaler = MinMaxScaler(feature_range=(-1, 1))\n",
    "        scaler.fit(X_all_factor_data)\n",
    "        X = scaler.transform(X_all_factor_data)\n",
    "        index_list = X_all_factor_data.index\n",
    "        del X_all_factor_data\n",
    "        X_all_factor_data = pd.DataFrame(X,index=index_list,columns=X_factor_list)\n",
    "    elif scaler_type == 'Standard':\n",
    "        scaler = StandardScaler(feature_range=(-1, 1))\n",
    "        scaler.fit(X_all_factor_data)\n",
    "        X = scaler.transform(X_all_factor_data)\n",
    "        index_list = X_all_factor_data.index\n",
    "        del X_all_factor_data\n",
    "        X_all_factor_data = pd.DataFrame(X,index=index_list,columns=X_factor_list)   \n",
    "    elif scaler_type == 'none':\n",
    "        X_all_factor_data = X_all_factor_data\n",
    "    \n",
    "    return X_all_factor_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:22:34.089277Z",
     "start_time": "2018-08-28T06:22:34.085265Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_Y_data(raw_data,Y_factor=Y_factor_name ):\n",
    "    Y_all_factor_data = raw_data.loc[:,Y_factor]\n",
    "    return Y_all_factor_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-31T00:42:19.450459Z",
     "start_time": "2018-08-31T00:41:48.899217Z"
    }
   },
   "outputs": [],
   "source": [
    "raw_data = get_rawdata_sas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-31T00:42:28.367174Z",
     "start_time": "2018-08-31T00:42:19.969340Z"
    }
   },
   "outputs": [],
   "source": [
    "# drop year 1957&2018,because the peper data begins in March 1957 end in Dec 2016\n",
    "raw_data = raw_data[raw_data['year']>=1958]\n",
    "raw_data = raw_data[raw_data['year']<=2017]\n",
    "raw_data['DATE'] = pd.to_datetime(raw_data['DATE'],format='%Y%m')\n",
    "raw_data.set_index('DATE',drop=True,inplace=True)\n",
    "raw_data.sort_index(inplace=True)\n",
    "raw_data['id'] = range(len(raw_data))\n",
    "raw_data.fillna(value=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:28.549086Z",
     "start_time": "2018-08-28T06:23:19.453902Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "F:\\anaconda\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "top_1000_data = get_top_data(raw_data)\n",
    "top_1000_data.sort_values('id',inplace=True)\n",
    "del top_1000_data['num']\n",
    "del top_1000_data['id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:28.555104Z",
     "start_time": "2018-08-28T06:23:28.551093Z"
    }
   },
   "outputs": [],
   "source": [
    "drop_lists =['permno', 'year','EXCHCD','sic2','b_m', 'tbl', 'ntis', 'svar', 'dp', 'ep_macro', 'tms', 'dfy']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:28.561118Z",
     "start_time": "2018-08-28T06:23:28.558112Z"
    }
   },
   "outputs": [],
   "source": [
    "drop_lists.append(Y_factor_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:30.508296Z",
     "start_time": "2018-08-28T06:23:28.566133Z"
    }
   },
   "outputs": [],
   "source": [
    "top_1000_data_X = get_X_data(top_1000_data,drop_list=drop_lists)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:30.514311Z",
     "start_time": "2018-08-28T06:23:30.510301Z"
    }
   },
   "outputs": [],
   "source": [
    "top_1000_data_Y = get_Y_data(top_1000_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:30.520329Z",
     "start_time": "2018-08-28T06:23:30.516318Z"
    }
   },
   "outputs": [],
   "source": [
    "data = dict({'X':top_1000_data_X,'Y':top_1000_data_Y})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:30.528350Z",
     "start_time": "2018-08-28T06:23:30.524339Z"
    }
   },
   "outputs": [],
   "source": [
    "del raw_data['id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:40.068717Z",
     "start_time": "2018-08-28T06:23:30.530356Z"
    }
   },
   "outputs": [],
   "source": [
    "all_data_X = get_X_data(raw_data,drop_list=drop_lists)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:40.074733Z",
     "start_time": "2018-08-28T06:23:40.071726Z"
    }
   },
   "outputs": [],
   "source": [
    "all_data_Y = get_Y_data(raw_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:40.079747Z",
     "start_time": "2018-08-28T06:23:40.076739Z"
    }
   },
   "outputs": [],
   "source": [
    "all_data = dict({'X':all_data_X,'Y':all_data_Y})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:44.027245Z",
     "start_time": "2018-08-28T06:23:40.082755Z"
    }
   },
   "outputs": [],
   "source": [
    "file = open(Path + '\\\\data\\\\alldata_demo_top1000.pkl', 'wb')\n",
    "pickle.dump(data, file)\n",
    "file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:59.434511Z",
     "start_time": "2018-08-28T06:23:44.029250Z"
    }
   },
   "outputs": [],
   "source": [
    "file = open(Path + '\\\\data\\\\alldata_demo.pkl', 'wb')\n",
    "pickle.dump(all_data, file)\n",
    "file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-28T06:23:59.511713Z",
     "start_time": "2018-08-28T06:23:59.440524Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count   56526.000\n",
       "mean        0.090\n",
       "std         0.401\n",
       "min        -1.000\n",
       "25%        -0.130\n",
       "50%         0.061\n",
       "75%         0.268\n",
       "max        25.248\n",
       "Name: ret_annual, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_1000_data_Y.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "position": {
    "height": "367.4px",
    "left": "1166px",
    "right": "20px",
    "top": "120px",
    "width": "350px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
